﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class FlowSortDao
    {
        public bool Delete(SqlConnection conn, int sortId)
        {
            string commandText = "DECLARE @RET bit IF EXISTS(SELECT [FlowId] FROM [dbo].[Flow_Type] WHERE [FlowSort] = @SortId) SET @RET = 0 ELSE BEGIN DELETE FROM [dbo].[Flow_Sort] WHERE [SortId] = @SortId SET @RET = 1 END SELECT @RET";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SortId", SqlDbType.Int) };
            commandParameters[0].Value = sortId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public bool Insert(SqlConnection conn, FlowSortData data)
        {
            string commandText = "INSERT [dbo].[Flow_Sort] ( [SortNo], [SortName] ) VALUES ( @SortNo, @SortName )";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@SortNo", SqlDbType.Int);
            commandParameters[0].Value = data.SortNo;
            commandParameters[1] = new SqlParameter("@SortName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.SortName;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            string commandText = "SELECT [SortId], [SortNo], [SortName] FROM [dbo].[Flow_Sort] ORDER BY [SortNo] ASC, [SortId] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Flow_Sort";
            return set.Tables[0];
        }

        public DataTable SelectAllWithNewPopedom(SqlConnection conn, string userId)
        {
            string commandText = "DECLARE @DeptId nvarchar(20) DECLARE @PrivId nvarchar(20) SELECT @DeptId = CAST([DeptId] AS nvarchar(20)),@PrivId = CAST([PrivId] AS nvarchar(20)) FROM [dbo].[Users] WHERE [UserId] = @UserId SELECT DISTINCT [Flow_Sort].[SortId], [Flow_Sort].[SortNo], [Flow_Sort].[SortName] FROM [dbo].[Flow_Sort], [dbo].[Flow_Type] WHERE [Flow_Sort].[SortId] = [Flow_Type].[FlowSort] AND ( ([Flow_Type].[FlowId] IN ( SELECT DISTINCT [FlowId] FROM [dbo].[Flow_Process] WHERE (CAST([PrcsDept] AS nvarchar(2000)) = 'ALL_DEPT' OR (CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([PrcsUser] AS nvarchar(4000)),'') + ',') > 0 OR CHARINDEX(',' + @DeptId + ',',',' + ISNULL(CAST([PrcsDept] AS nvarchar(2000)),'') + ',') > 0 OR CHARINDEX(',' + @PrivId + ',',',' + ISNULL(CAST([PrcsPriv] AS nvarchar(2000)),'') + ',') > 0 )) AND [PrcsId] = 1 ) ) OR ([FlowType] = 2) ) ORDER BY [SortNo] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Sort";
            return set.Tables[0];
        }

        public DataTable SelectBySortId(SqlConnection conn, int sortId)
        {
            string commandText = "SELECT [SortId], [SortNo], [SortName] FROM [dbo].[Flow_Sort] WHERE [SortId] = @SortId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SortId", SqlDbType.Int) };
            commandParameters[0].Value = sortId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Sort";
            return set.Tables[0];
        }

        public DataTable SelectQuery(SqlConnection conn, string userId, bool isAdministrator)
        {
            string commandText = "IF @IsAdministrator =1 BEGIN SELECT [SortId], [SortNo], [SortName] FROM [dbo].[Flow_Sort] ORDER BY [SortNo] ASC END ELSE BEGIN DECLARE @DeptId nvarchar(20) DECLARE @PrivId nvarchar(20) SELECT @DeptId = CAST([DeptId] AS nvarchar(20)),@PrivId = CAST([PrivId] AS nvarchar(20)) FROM [dbo].[Users] WHERE [UserId] = @UserId SELECT DISTINCT [Flow_Sort].[SortId], [Flow_Sort].[SortNo], [Flow_Sort].[SortName] FROM [dbo].[Flow_Sort], [dbo].[Flow_Type] WHERE [Flow_Sort].[SortId] = [Flow_Type].[FlowSort] AND ( ([Flow_Type].[FlowId] IN ( SELECT DISTINCT [FlowId] FROM [dbo].[Flow_Process] WHERE CAST([PrcsDept] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([PrcsUser] AS nvarchar(4000)),'') + ',') > 0 OR CHARINDEX(',' + @DeptId + ',',',' + ISNULL(CAST([PrcsDept] AS nvarchar(2000)),'') + ',') > 0 OR CHARINDEX(',' + @PrivId + ',',',' + ISNULL(CAST([PrcsPriv] AS nvarchar(2000)),'') + ',') > 0 ) ) OR ([FlowType] = 2) OR ( CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Flow_Type].[ManageUser] AS nvarchar(2000)),'') + ',') > 0 OR CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Flow_Type].[QueryUser] AS nvarchar(2000)),'') + ',') > 0 ) ) ORDER BY [SortNo] ASC END";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[1].Value = isAdministrator;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Sort";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, FlowSortData data)
        {
            string commandText = "UPDATE [dbo].[Flow_Sort] SET [SortNo] = @SortNo, [SortName] = @SortName WHERE [SortId] = @SortId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@SortId", SqlDbType.Int);
            commandParameters[0].Value = data.SortId;
            commandParameters[1] = new SqlParameter("@SortNo", SqlDbType.Int);
            commandParameters[1].Value = data.SortNo;
            commandParameters[2] = new SqlParameter("@SortName", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.SortName;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

